In 2021 I carried a case study for a German start-up. The aim was to
calculate the Return on Ads Spending (ROAS) for several Facebook
campaigns across the globe and delivering with key insights and
recommendations for the upper management. There were two main channels
for campaigns spending, the Facebook App and the Facebook website.
The ROAS is a KPI that represents the ratio of the total revenue that
can be attributed to a specific campaign divided by the total
expenditure of the campaign. My client defined 0.9 as the threshold - a
ROAS higher than 0.9 was set as an acceptable return on investment.
More concisely:
ROAS = TOTAL CAMPAIGN REVENUE / CAMPAIGN COST Where:
Revenue = all attributable subscriptions by campaign
Cost = All cost of the campaignTo calculate the ROAS, I performed several postgreSQL queries on
their servers.
select c.sub_region,
e.campaign_id,
sum(a.revenue) as revenue,
g.spend as spend
from
fbt_conversions_web as a,
fbt_web_user_mapping as b,
fbt_web_sessions as c,
(select distinct d.campaign_id
from fbt_facebook_campaigns d) as e,
(select sub_region, campaign_id, sum(spend) as spend
from fbt_facebook_campaigns f
group by
sub_region,
campaign_id) as g
where
a.sk_user = b.sk_user
and
b.sk_web_user = c.sk_web_user
and
c.campaign_id = e.campaign_id
and
c.sub_region = g.sub_region
and
c.campaign_id = g.campaign_id
group by
e.campaign_id,
c.sub_region,
g.spend;
# A tibble: 5 × 6
...1 sub_region campaign_id revenue spend WEB_ROAS
<dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 0 Australasia & Asia 280020 7359. 7213 1.02
2 1 Northern America 280020 20774. 28836 0.720
3 2 Northern & Western Europe 280020 10220. 16404 0.623
4 3 South & Central America 280020 4219. 4623 0.913
5 4 Southern & Eastern Europe 280020 8425. 11906 0.708The Facebook web campaign n° 280020 had a ROAS of 0.8, falling below
the threshold defined by the client. However, there was substantial
variation across regions. Particularly, Australasia & Asia and South
& Central America are markets with a ROAS higher than 0.9.
The overall performance of the web campaign was 0.8. Regionwise, the
Northern & Western Europe region had the a weakest performance
(0.62). On the other side, Australasia & Asia had the stronges ROAS
of 1.02.
Blue line = mean Facebook Web ROAS
Red line = 0.9 threshold c.campaign_id,
sum(a.revenue) rev_web,
d.revenue_app,
e.spend
from
fbt_conversions_web a,
fbt_installs b,
(select distinct campaign_id
from
fbt_facebook_campaigns) c,
(select fbt_installs.campaign_id,
sum(fbt_conversions_app.revenue) revenue_app
from
fbt_conversions_app,
fbt_installs
where
fbt_conversions_app.sk_user = fbt_installs.sk_user
group by
fbt_installs.campaign_id
) d,
(select sum(spend) spend,campaign_id
from fbt_facebook_campaigns
group by campaign_id) e
where
a.sk_user =b.sk_user
and
b.campaign_id =c.campaign_id
and
c.campaign_id = d.campaign_id
and
c.campaign_id = e.campaign_id
group by
e.spend,
d.revenue_app,
c.campaign_id;# A tibble: 3 × 6
...1 campaign_id rev_web revenue_app spend ROAS
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 0 339703 654. 44215. 46391 0.967
2 1 379581 267. 24028. 63239 0.384
3 2 279755 987. 155108. 155666 1.00 Each Facebook App campaign had a different performance. Clarly, the campaign 379581 fell drastically below the expected ROAS. But how was the geographic distribution of these performances? Are they all equal? Following the previous results, I expected to get high variation.
select
f.sub_region,
f.campaign_id,
sum(a.revenue) app_revenue,
f.spend spend
from
fbt_conversions_app a,
fbt_installs b,
(select
distinct c.campaign_id
from fbt_facebook_campaigns c) d,
(select
sub_region, campaign_id, sum(spend) spend
from fbt_facebook_campaigns
group by
sub_region,
campaign_id) f
where
a.sk_user = b.sk_user
and
b.campaign_id = d.campaign_id
and
b.campaign_id =f.campaign_id
and
b.sub_region = f.sub_region
group by
f.campaign_id,
f.sub_region,
f.spend;# A tibble: 11 × 6
...1 sub_region campaign_id app_revenue spend APP_ROAS
<dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 0 Australasia & Asia 279755 13213. 12651 1.04
2 1 Northern America 279755 40209. 49692 0.809
3 2 Northern & Western Europe 279755 65595. 52304 1.25
4 3 South & Central America 279755 19061. 23106 0.825
5 4 Southern & Eastern Europe 279755 17030. 17913 0.951
6 5 Australasia & Asia 339703 2542. 2488 1.02
7 6 Northern America 339703 10074. 8644 1.17
8 7 Northern & Western Europe 339703 21642. 23544 0.919
9 8 South & Central America 339703 5626. 6107 0.921
10 9 Southern & Eastern Europe 339703 4331. 5608 0.772
11 10 Northern America 379581 23685. 63239 0.375The mean ROAS for the Facebook App campaigns was of 0.91, being 0.11
points higher than of the Facebook Page. However, the table shows great
variation across regions. Australasia & Asia have a ROAS higher than
0.9, which is consistent with the Facebook website campaign results. The
Northern & Western Europe region had a healthy performance on the
two app campaigns. Southern & Eastern Europe, South & Central
America and Northern America fell in an intermediate category, with some
campaigns underperforming whereas others showed positive results.
The following plots display the ROAS of the App campaigns by
regions. Remember that the overall performance of the Facebook App
channel ROAS is 0.91. The plot shows, however, that campaign ID 37981,
in North America, underperformed with a ROAS of 0.37.
Blue line = mean Facebook App ROAS
Red line = 0.9 thresholdThree main patterns were found in the analysis:
That App campaigns perform 13,75% better than web ads.
Web campaigns have an average ROAS of 0.8, with a minimum of 0.62 (Northern & Western Europe) and a maximum ROAS value of 1.02 (Australasia & Asia).
App campaigns had an average of 0.91, with a minimum ROAS of 0.38 (Campaign id 379581 - Northern America ) and a maximum of 1.25 (Campaign id 279755 - Northern & Western Europe).
Recommendations:
Increase spending in App campaigns. Focus spending on regions more responsive to App advertising such as “Northern & Western Europe” and “Australasia & Asia.”
Decrease spending in the lower-performing regions, specifically in the “Northern & Western Europe” and in “Southern & Eastern Europe.”